package com.wh1200.mybatis.xmlless.model

import com.wh1200.kotlin.containsAny
import com.wh1200.kotlin.isNotNullAndNotBlank
import com.wh1200.kotlin.toCamelCase
import com.wh1200.mybatis.xmlless.annotations.DeleteValue
import com.wh1200.mybatis.xmlless.constant.*
import com.wh1200.mybatis.xmlless.dialect.DialectFactory
import com.wh1200.mybatis.xmlless.enums.MethodType
import com.wh1200.mybatis.xmlless.enums.TestType
import com.wh1200.mybatis.xmlless.enums.XmllessFieldFill
import com.wh1200.mybatis.xmlless.enums.XmllessFieldStrategy
import com.wh1200.mybatis.xmlless.enums.XmllessFieldStrategy.ALWAYS
import com.wh1200.mybatis.xmlless.model.base.BaseCriteria
import com.wh1200.mybatis.xmlless.model.component.*
import com.wh1200.mybatis.xmlless.model.criteria.OptionalParam
import com.wh1200.mybatis.xmlless.model.exception.BuildSQLException
import com.wh1200.mybatis.xmlless.model.visitor.ReplaceToParamVisitor
import com.wh1200.mybatis.xmlless.resolver.ColumnsResolver
import com.wh1200.mybatis.xmlless.resolver.ExpressionResolver
import com.wh1200.mybatis.xmlless.util.ColumnUtils
import com.wh1200.mybatis.xmlless.util.ScriptUtils
import com.wh1200.mybatis.xmlless.util.indent
import net.sf.jsqlparser.expression.operators.relational.EqualsTo
import net.sf.jsqlparser.parser.CCJSqlParserUtil
import net.sf.jsqlparser.schema.Column


/**
 *
 * @author 吴昊
 * @since 0.0.1
 */
data class Query(
    /** 待解析的方法 */
    val methodInfo: MethodInfo,
    /**  sql类型 */
    val type: MethodType,
    /**  更细或者查询的属性列表 */
    val properties: Properties = Properties(),
    /**  查询条件信息 */
    val criterion: List<BaseCriteria> = listOf(),
    /**  排序信息 */
    val sorts: List<SortInfo> = listOf(),
    /** group by */
    val groupBy: List<String>,
    /** having 条件 */
    val havingConditions: List<BaseCriteria>,
    /**  limit信息 */
    var limitation: Limitation? = null
) {

  /**  数据对象的与数据库的映射管理 */
  val mappings: FieldMappingList = methodInfo.mappings

  /**  来自Page参数的排序条件 */
  var extraSortScript: String? = null


  init {
    if (type !in listOf(MethodType.Select, MethodType.Count) && groupBy.isNotEmpty()) {
      error("方法${methodInfo.fullMethodName}的不是查询方法，不能使用groupBy")
    }
  }

  fun buildCountSql(): String {
    // 必须先解析查询条件才能确定条件中用于判断的字段来自哪张表
    val where = resolveWhere()
    val from = resolveFrom(true)
    val column = if (this.properties.includes.size == 1) {
      val property = this.properties.includes.first()
      ColumnsResolver.resolveColumnByPropertyName(
          property, methodInfo
      ).first().toSqlWithoutAlias()
    } else {
      "*"
    }
    return CountDeclaration(
        column, from,
        where, this.methodInfo.scriptAppend
    ).toSql(methodInfo)
  }

  /**
   * 对查询条件进行分组，类似findByNameOrDescriptionLikeKeywords的表达式，
   * 由于name未指明条件类型，自动将name和description归并为一组，并且设置条件类型和description一样为like
   * 同组条件如果超过一个在构建查询语句时添加括号
   */
  fun resolveGroups(): List<BaseCriteria> {
    return criterion
  }

  fun toSql(): String {
    return when (type) {
      MethodType.Delete      -> buildDeleteSql()

      MethodType.Clean       -> buildCleanSql()

      MethodType.InsertOrUpdate,
      MethodType.Insert      -> buildInsertSql()

      MethodType.SelectDistinct,
      MethodType.Select      -> buildSelectSql()

      MethodType.Update      -> buildUpdateSql()
      MethodType.Count       -> buildCountSql()

      MethodType.LogicDelete -> buildLogicDeleteSql()
    }
  }

  private fun buildCleanSql(): String {
    return "TRUNCATE TABLE " + this.tableName().getAliasOrName()
  }

  fun containedTables(): List<String> {
    return (this.criterion.map { it.getTableNames() }.flatten().distinct()
        + ColumnsResolver.resolveIncludedTables(properties, methodInfo)).distinct()
  }

  private var fromDeclaration: FromDeclaration? = null

  /**
   * @param isCount 移除没有被条件引用的join表
   */
  fun resolveFrom(isCount: Boolean = false): FromDeclaration {
    if (fromDeclaration != null) {
      return fromDeclaration!!
    }
    val onlyIncludesTables = if (isCount) {
      criterion.asSequence().map {
        it.getColumns().mapNotNull { column -> column.table }
      }.flatten().distinct().toList()
    } else {
      null
    }
    fromDeclaration = fromDeclaration(containedTables(), onlyIncludesTables)
    return fromDeclaration!!
  }

  private fun buildUpdateSql(): String {
    if (methodInfo.scriptAppend.isBlank()) {
      return UPDATE_NO_APPEND.format(
          tableName().name,
          resolveUpdateProperties(false),
          resolveUpdateWhere().trim()
      )
    }
    return UPDATE.format(
        tableName().name,
        resolveUpdateProperties(false),
        resolveUpdateWhere().trim(),
        methodInfo.scriptAppend
    )
  }

  private fun convertIf(
      sqlScript: String, property: String, mapping: FieldMapping,
      param: OptionalParam?
  ): String? {
    val strategy = mapping.tableFieldInfo.updateStrategy
    if (strategy == ALWAYS) {
      return sqlScript
    }
    if (strategy == XmllessFieldStrategy.NEVER) {
      return null
    }
    if (mapping.selectIgnore || mapping.isDeleteOrUpdateInfoField()) {
      return sqlScript
    }
    val isInputNotNull = param != null && (param.element.isNotNull || param.element.update.isNotNullAndNotBlank())
    val test = TestConditionGroup()
    if (strategy == XmllessFieldStrategy.NOT_NULL) {
      if (!isInputNotNull) {
        test.add(TestConditionDeclaration(property, TestType.NotNull))
      }
    } else if (strategy == XmllessFieldStrategy.NOT_EMPTY) {
      test.add(TestConditionDeclaration(property, TestType.NotEmpty))
    } else {
      if (!isInputNotNull) {
        test.add(TestConditionDeclaration(property, TestType.NotNull))
      }
      if (mapping.tableFieldInfo.updateStrategy == XmllessFieldStrategy.NOT_EMPTY
          && mapping.tableFieldInfo.isCharSequence && (param == null || !param.element.isNotEmpty)
      ) {
        test.add(TestConditionDeclaration(property, TestType.NotEmpty))
      }
    }
    return ScriptUtils.convertIf(sqlScript, test.toSql(), true)
  }

  /**
   * 获取 set sql 片段
   *
   * @return sql 脚本片段
   * @author 吴昊
   */
  private fun getSqlSet(
      mapping: FieldMapping, insertUpdate: Boolean,
      setExpression: EqualsTo?, noIfWrap: Boolean
  ): String? {
    val column = mapping.tableFieldInfo.column
    var sqlSet = ColumnUtils.wrapColumn(column) + " = "
    if (mapping.isVersion) {
      if (insertUpdate) {
        return sqlSet + DialectFactory.getDialect().createUpsertFieldSetExpression(ColumnUtils.wrapColumn(column)) + " + 1,"
      } else {
        return sqlSet + ColumnUtils.wrapColumn(column) + " + 1,"
      }
    }
    if (setExpression?.rightExpression != null) {
      val visitor = ReplaceToParamVisitor(listOf(), methodInfo, true)
      setExpression.rightExpression.accept(visitor)
      sqlSet += setExpression.rightExpression.toString()
      sqlSet += ","
      return sqlSet
    }
    val param = methodInfo.findOptionalParam(mapping.property)
    if (param == null && !this.isBatch() && !mapping.isDeleteOrUpdateInfoField()) {
      return null
    }
    // 默认: column=
    when {

      param != null && param.element.update.isNotNullAndNotBlank() -> {
        sqlSet += param.element.update
      }

      mapping.tableFieldInfo.update.isNotNullAndNotBlank()         -> {
        sqlSet += mapping.tableFieldInfo.update.isNotNullAndNotBlank()
      }

      else                                                         -> {
        sqlSet += if (insertUpdate && mapping.tableFieldInfo.updateStrategy != ALWAYS) {
          DialectFactory.getDialect(methodInfo.dbType).createUpsertFieldSetExpression(column)
        } else {
          when {
            !mapping.tableFieldInfo.update.isNullOrBlank() -> mapping.tableFieldInfo.update.format(column)

            mapping.isDeletedDate
|| mapping.isLastModifiedDate                 -> "CURRENT_TIMESTAMP"

            mapping.isDeletedBy
|| mapping.isLastModifiedBy                   -> Strings.CURRENT_USER_HOLDER

            else                                           -> ScriptUtils.safeParam(
                mapping.getPropertyExpression(
                    null, false
                )
            )
          }
        }
      }
    }
    sqlSet += ","
    if (insertUpdate || setExpression != null || noIfWrap) {
      return sqlSet
    }
    return when (mapping.tableFieldInfo.fieldFill) {
      XmllessFieldFill.UPDATE,
      XmllessFieldFill.INSERT_UPDATE -> sqlSet

      else                           -> convertIf(
          sqlSet, mapping.tableFieldInfo.property, mapping, param
      )
    }
  }

  private fun resolveLimit(): String {
    if (limitation == null) {
      if (!methodInfo.limit.isNullOrBlank()) {
        return "LIMIT " + methodInfo.limit
      }
      return ""
    }
    val limit = limitation!!
    return DialectFactory.getDialect(methodInfo.dbType)
        .toLimitSql(
            limit.isSpringDataPageable,
            limit.pageableParamName,
            limit.offsetParam, limit.sizeParam
        )
  }

  private fun resolveOrder(): String {
    return when {
      this.sorts.isNotEmpty() -> {
        val string = this.sorts.joinToString(", ") {
          if (it.mode == SortInfo.Mode.Property) {
            ColumnsResolver.resolveColumnByPropertyName(
                it.value, methodInfo
            ).first().toSqlWithoutAlias() + " " + it.direction
          } else {
            it.value
          }
        }
        if (extraSortScript.isNullOrBlank()) {
          ORDER_BY.format(string)
        } else {
          ORDER_BY.format("$string, $extraSortScript")
        }
      }

      extraSortScript != null -> ORDER_BY.format(extraSortScript)
      else                    -> ""
    }
  }

  private fun resolveUpdateWhere(): String {
    return when {
      criterion.isEmpty() -> String.format(
          """
        WHERE %s = #{%s}
      """, mappings.tableInfo.keyColumn, mappings.tableInfo.keyProperty
      )

      else                -> resolveWhere().toSql()
    }
  }

  private fun buildDeleteSql(): String {
    return DELETE.format(
        tableName().name, resolveWhere().toSql(), methodInfo.scriptAppend
    )
  }

  private fun buildInsertSql(): String {
    val columns = mappings.insertFields(this.properties, this.isBatch())
    val values = when {
      isBatch()                       -> {
        mappings.insertProperties("item.", this.properties, true)
      }

      this.properties.isNotEmpty()    -> {
        mappings.insertProperties(insertProperties = this.properties)
      }

      this.properties.isIncludeEmpty()
          && this.criterion.isEmpty() -> {
        mappings.insertProperties()
      }

      else                            -> throw BuildSQLException(
          "无法解析${this.methodInfo.fullMethodName}"
      )
    }
    if (columns.size != values.size) {
      throw BuildSQLException("解析方法[${methodInfo.fullMethodName}]失败，插入的字段\n$columns\n与插入的值\n$values\n数量不一致")
    }
    return getInsertTemplate().format(
        mappings.tableName,
        ScriptUtils.convertTrim(columns.joinToString("\n\t"), ","),
        ScriptUtils.convertTrim(values.joinToString("\n\t"), ","),
        resolveUpdateProperties(this.type == MethodType.InsertOrUpdate)
    )
  }

  private fun isBatch(): Boolean {
    if (this.methodInfo.parameters.size != 1) {
      return false
    }
    val firstParam = this.methodInfo.parameters.first()
    return firstParam.isCollection || firstParam.isArray
  }

  private fun getInsertTemplate(): String {
    when {
      isBatch()                        -> {
        return if (type == MethodType.InsertOrUpdate) {
          DialectFactory.getDialect(methodInfo.dbType).getBatchInsertOrUpdateTemplate()
        } else {
          BATCH_INSERT
        }
      }


      this.properties.isNotEmpty()
          || (this.properties.isIncludeEmpty()
          && this.criterion.isEmpty()) -> {
        return if (type == MethodType.InsertOrUpdate) {
          DialectFactory.getDialect(methodInfo.dbType).getInsertOrUpdateTemplate()
        } else {
          INSERT
        }
      }

      else                             -> throw BuildSQLException(
          "无法解析${this.methodInfo.fullMethodName}"
      )
    }
  }

  private fun buildLogicDeleteSql(): String {
    val mapper = this.mappings.mappings.find { it.tableFieldInfo.isLogicDelete }
      ?: throw IllegalStateException("缺少逻辑删除字段，请在字段上添加@TableLogic注解")
    val value = this.mappings.getLogicDelFlagValue(DeleteValue.Deleted)
    val builder = StringBuilder()
        .append("UPDATE")
        .appendLine().append(" ")
        .appendLine(tableName().name)
        .appendLine("SET")
    val setDeletedBy = this.mappings.mappings.find { it.isDeletedBy }?.let {
      getSqlSet(
          it, false, null, true
      )
    }
    val setDeletedDate = this.mappings.mappings.find { it.isDeletedDate }?.let {
      getSqlSet(
          it, false, null, true
      )
    }
    if (setDeletedBy.isNullOrBlank() && setDeletedDate.isNullOrBlank()) {
      builder.appendLine("  ${mapper.column} = ${wrapSpecificValue(value)}")
    } else {
      val setSql = StringBuilder()
      setSql.append("  ${mapper.column} = ${wrapSpecificValue(value)},")
      if (!setDeletedBy.isNullOrBlank()) {
        setSql.appendLine().append("  ").append(setDeletedBy)
      }
      if (!setDeletedDate.isNullOrBlank()) {
        setSql.appendLine().append("  ").append(setDeletedDate)
      }
      builder.appendLine(wrapSetScript(setSql.toString(), true).indent(1))
    }
    builder.appendLine(resolveWhere().toSql())
    if (methodInfo.scriptAppend.isNotBlank()) {
      builder.appendLine(methodInfo.scriptAppend)
    }
    return builder.toString()
  }

  private fun wrapSpecificValue(value: Any?): String {
    if (value is String) {
      return "'$value'"
    }
    return value.toString()
  }

  /**
   * 构建select查询语句
   */
  private fun buildSelectSql(): String {
    return SelectDeclaration(
        distinct = type == MethodType.SelectDistinct,
        ColumnsResolver.resolveSelectColumns(properties, this.methodInfo),
        resolveFrom(),
        resolveWhere(),
        resolveGroupBy(mappings),
        resolveOrder(),
        resolveHaving(),
        resolveLimit()
    ).toSql(methodInfo)
  }

  private fun resolveHaving(): String {
    if (havingConditions.isEmpty()) {
      return ""
    }
    return HAVING.format(QueryCriteriaGroup(havingConditions, methodInfo).toSqlWithoutTest())
  }

  /**
   * 获取from后的表名称及join信息的语句
   * 例如： t_student LEFT JOIN t_score ON t_score.student_id = t_student.id
   */
  private fun fromDeclaration(
      includedTableAlias: List<String>,
      onlyIncludesTables: List<TableName>?
  ): FromDeclaration {
    val tableName = TableName(mappings.tableName, "")
    val joins = mappings.selectJoins(
        level = 1,
        includedTableAlias,
        null,
        onlyIncludesTables
    ).toMutableList()
    val existsJoinTables = joins.map { it.joinCondition.originTable.getAliasOrName() }
    if (mappings != methodInfo.modelMappings) {
      joins.addAll(
          methodInfo.modelMappings.selectJoins(
              level = 2,
              includedTableAlias.filter {
                it != mappings.tableName && it !in existsJoinTables
              }, null, onlyIncludesTables, deepJoin = false
          )
      )
    }
    joins.forEach {
      if (it.joinCondition.originTable.name == tableName.name) {
        it.joinCondition.originTable = tableName
      }
    }
    return FromDeclaration(tableName, joins.distinctBy { it.joinTable.getAliasOrName() })
  }

  private fun resolveGroupBy(mappings: FieldMappingList): String {
    val mappingList = when {
      properties.isIncludeNotEmpty() -> mappings.mappings.filter { it.property in properties }

      else                           -> mappings.mappings.filter { it.property !in properties.excludes }
    }
    val groupProperties = mappingList.mapNotNull { it.joinInfo }.filterIsInstance<PropertyJoinInfo>()
        .mapNotNull { it.groupBy } +
        groupBy.map { ExpressionResolver.parseExpression(it, methodInfo).first }
    return when {
      groupProperties.isNotEmpty() -> "GROUP BY\n\t${groupProperties.joinToString(", ")}"
      else                         -> ""
    }
  }

  private fun resolveUpdateProperties(isInsertUpdate: Boolean): String {
    if (type == MethodType.Insert) {
      return ""
    }

    if (methodInfo.updateSetList.isNotEmpty()) {
      val list = methodInfo.updateSetList.map {
        if (it.lowercase().containsAny(" append ", " add ")) {
          val splits = it.split(" (append|add) ".toRegex(RegexOption.IGNORE_CASE))
          if (splits.size != 2) {
            error("非法的表达式${it}")
          }
          val property = splits[0]
          val value = splits[1]
          val mapping = mappings.mappings.find {
            it.property == property
          } ?: error("无法识别的更新字段${property}")
          val column = ColumnUtils.wrapColumn(mapping.column)
          var sqlSet = "$column = "
          if (!value.startsWith("#")) {
            error("非法的表达式${it}")
          }
          sqlSet += DialectFactory.getDialect(methodInfo.dbType).createJsonAddExpression(
              column, value.drop(1)
          )
          sqlSet += ","
          sqlSet
        } else {
          when (val set = CCJSqlParserUtil.parseExpression(it)) {
            is Column   -> {
              val mapping = mappings.mappings.find {
                it.property == set.columnName
              } ?: error("无法识别的更新字段${set.columnName}")
              getSqlSet(
                  mapping, isInsertUpdate, null,
                  methodInfo.ignoreIfWrapFields.contains(mapping.property)
              )?.indent()
            }

            is EqualsTo -> {
              val left = set.leftExpression
              if (left !is Column) {
                error("无法解析方法字段更新表达式$set")
              }
              if (left.columnName.startsWith("$")) {
                getDynamicColumnSet(left, set)?.indent()
              } else {
                val mapping = mappings.mappings.find {
                  it.property == left.columnName
                } ?: error("无法识别的更新字段${left.columnName}")
                getSqlSet(
                    mapping, isInsertUpdate, set,
                    methodInfo.ignoreIfWrapFields.contains(mapping.property)
                )?.indent()
              }
            }

            else        -> {
              error("无法解析方法字段更新表达式$set")
            }
          }
        }
      }.toMutableList()
      val versionMapping = mappings.mappings.find { it.isVersion }
      if (versionMapping != null) {
        list += getSqlSet(
            versionMapping, isInsertUpdate, null, true
        )?.indent()
      }
      return wrapSetScript(list.joinToString("  "), isInsertUpdate)
    } else if (this.properties.isIncludeEmpty()) {
      val selectedFieldMappings = mappings.mappings.filter {
        (it.joinInfo == null && !it.updateIgnore && it.property !in this.properties.excludes
            && it.property != mappings.tableInfo.keyProperty) || it.isVersion
      }
      val list = selectedFieldMappings.mapNotNull {
        getSqlSet(
            it, isInsertUpdate, null,
            methodInfo.ignoreIfWrapFields.contains(it.property)
        )?.indent()
      }
      return wrapSetScript(list.joinToString("  "), isInsertUpdate)
    }
    val updateExpressions = this.mappings.mappings.filter { !it.updateIgnore }.filter {
      it.property in this.properties.includes || it.isDeleteOrUpdateInfoField() || it.isVersion
    }.mapNotNull { mapping ->
      getSqlSet(
          mapping, isInsertUpdate, null,
          methodInfo.ignoreIfWrapFields.contains(mapping.property)
      )
    }.toMutableList()
    this.properties.includes.filter {
      it !in this.mappings.mappings.map { mapping -> mapping.property }
    }.forEach {
      resolveJsonUpdateExpression(it, updateExpressions)
    }
    return wrapSetScript(updateExpressions.joinToString("  "), isInsertUpdate)
  }

  private fun getDynamicColumnSet(column: Column, set: EqualsTo): String? {
    if (set.rightExpression == null) {
      throw BuildSQLException("无法解析动态字段更新表达式$set")
    }
    val visitor = ReplaceToParamVisitor(listOf(), methodInfo, true)
    set.rightExpression.accept(visitor)
    return if (column.table?.name.isNullOrBlank()) {
      "\${${column.columnName.drop(1)}} = ${set.rightExpression},"
    } else {
      val tableName = column.table.name
      val mapping = mappings.mappings.find {
        it.property == tableName
      } ?: error("无法识别的更新字段$column")
      "${mapping.column} = JSON_SET(${mapping.column}, '$.\${${column.columnName.drop(1)}}', ${set.rightExpression}),"
    }
  }

  private fun resolveJsonUpdateExpression(property: String, updateExpressions: MutableList<String>) {
    when {
      property.endsWith("SetNull")  -> {
        val propertyName = property.dropLast("SetNull".length)
        val mapping = this.mappings.mappings.find { it.property == propertyName }
        if (mapping != null) {
          val column = mapping.tableFieldInfo.column
          updateExpressions.add("$column = null")
        }
      }

      property.endsWith("SetEmpty") -> {
        val propertyName = property.dropLast("SetEmpty".length)
        val mapping = this.mappings.mappings.find { it.property == propertyName }
        if (mapping != null) {
          val column = mapping.tableFieldInfo.column
          updateExpressions.add("$column = '[]'")
        }
      }

      property.contains("Remove")   -> {
        resolveJsonRemove(property, updateExpressions)
      }

      property.contains("Add")      -> {
        resolveJsonAdd(property, updateExpressions)
      }
    }
  }

  private fun resolveJsonAdd(property: String, updateExpressions: MutableList<String>) {
    val splits = property.split("Add")
    if (splits.size != 2) {
      return
    }
    val propertyName = splits[0]
    val parameterName = splits[1].toCamelCase()
    val mapping = this.mappings.mappings.find { it.property == propertyName }
    if (mapping != null) {
      val column = mapping.tableFieldInfo.column
      updateExpressions.add(
          "$column = " + DialectFactory.getDialect(methodInfo.dbType)
              .createJsonAddExpression(column, parameterName)
      )
    }
  }

  private fun resolveJsonRemove(property: String, updateExpressions: MutableList<String>) {
    val splits = property.split("Remove")
    if (splits.size != 2) {
      return
    }
    val propertyName = splits[0]
    val parameterName = splits[1].toCamelCase()
    val mapping = this.mappings.mappings.find { it.property == propertyName }
    if (mapping != null) {
      val column = mapping.tableFieldInfo.column
      updateExpressions.add(
          "$column = " + DialectFactory.getDialect(methodInfo.dbType)
              .createJsonRemoveExpression(column, parameterName, mapping.isNumberType)
      )
    }
  }

  private var whereDeclaration: WhereDeclaration? = null

  private fun resolveWhere(): WhereDeclaration {
    if (whereDeclaration != null) {
      return whereDeclaration!!
    }
    val groups = resolveGroups()
    val defaultWrapWithIf = groups.count { it !is LogicDeleteCriteria } > 1
        && (this.methodInfo.parameters.size != 1 || this.methodInfo.hasOnlyComplexParameter)
    val whereAppend = methodInfo.whereAppend
    whereDeclaration = WhereDeclaration(whereAppend, groups, defaultWrapWithIf)
    return whereDeclaration!!
  }

  private fun tableName(): TableName {
    return TableName(mappings.tableName, mappings.tableName.replace('.', '_'))
  }

  private fun wrapSetScript(sql: String, noSetPrefix: Boolean): String {
    val prefix = when {
      noSetPrefix -> ""
      else        -> "SET"
    }

    return ScriptUtils.convertTrim(sql, prefix, null, null, ",")
  }

}
